package dao.studentdao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dao.basedao.DBManager;
import model.Student;

public class StudentDaoImpl implements StudentDao{

	@Override
	public void addStudent(Student student) throws SQLException {
		Connection con=DBManager.getConn();
		PreparedStatement ps=con.prepareStatement("insert into user_info(user_id,name,schoolyear,role_id) values(?,?,?,?)");
		ps.setString(1, student.getStu_id());
		ps.setString(2, student.getName());
		ps.setString(3,student.getYear());
		ps.setInt(4, student.getRole_id());
		ps.executeUpdate();
		ps.close();
		DBManager.closeConn(con);
	}

	@Override
	public ArrayList<Student> queryAllStudent(String year) throws Exception {
		ArrayList<Student> list=new ArrayList<Student>();
		Connection con=DBManager.getConn();
		PreparedStatement ps=con.prepareStatement("select * from user_info where schoolyear= ?");
		ps.setString(1, year);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			Student student=new Student();
			student.setStu_id(rs.getString("user_id"));
			student.setName(rs.getString("name"));
			student.setRole_id(rs.getInt("role_id"));
			student.setYear(year);
			list.add(student);
		}
		ps.close();
		DBManager.closeConn(con);
		return list;
	}

}
